<!-- If the browser does not support frames put the first topic content here --> <div class="Section1"> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span> <span style='color:windowtext'>DEVELOPPEMENT CLIENT-SERVEUR EN JAVA</span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <h1><a name="node0" id="node0"></a>1<span style= 'font:7.0pt "Times New Roman"'> </span> Présentation</h1> <p class="chap" style='border:none'><u><span style= 'font-size:12.0pt;color:black'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>JDBC (Java DataBase Connectivity) est voisin d’ODBC. L’API JDBC est l’ensemble des classes et interfaces permettant de travailler (connexion à la base, exécution de requêtes et de procédures et fonctions stockées, exploitation des résultats renvoyés…) sur des bases de données relationnelles dans des programmes Java.</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>Les classes et interfaces de l'API JDBC figurent dans le package java.sql.</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il est indispensable d’importer l’API JDBC dans tout programme se servant de la technologie JDBC ; pour cela, il faut écrire :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>import java.sql.* ;</span></b></p> <p class="MsoHeader"> </p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut dans un premier temps établir une connexion à la base puis effectuer les actions nécessaires sur les données.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'>Remarque concernant le package java.sql :</span></u></p> <p class="MsoHeader">En fait, le paquetage java.sql contient principalement des interfaces. Pour être utilisées, ces interfaces devront être mises en œuvre (implémentées) dans la classe correspondant au pilote permettant l’accès à la base de données.</p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoHeader"> </p> <h1><a name="node1" id="node1"></a>2<span style= 'font:7.0pt "Times New Roman"'> </span> Connexion à la base de données</h1> <p class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'><span style= 'text-decoration:none'> </span></span></u></b></p> <h2><a name="node2" id="node2"></a>2.1<span style= 'font:7.0pt "Times New Roman"'> </span> Types de pilotes</h2> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il existe un ou plusieurs drivers JDBC spécifiques pour chaque SGBD (système de gestion de bases de données : Access, Oracle, Informix, ...).</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Les pilotes JDBC sont classés en 2 familles :</span></p> <p class="MsoNormal" style= 'margin-left:14.2pt;text-indent:-14.2pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-family:"Times New Roman"'>La famille de pilotes non 100% Java :</span></u></b><span style= 'font-family:"Times New Roman"'>ce sont des pilotes qui utilisent une partie écrite dans un autre langage que Java (souvent en C). Ces pilotes doivent être installés sur la station cliente. Dans cette 1<sup>ère</sup> famille, on distingue :</span></p> <ul type="disc"> <li class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Pilotes de type 1</span></b><span style='font-family:"Times New Roman"'>:</span> <span style='font-family:"Times New Roman"'>Pilotes accédant à une base de données par l'intermédiaire d'une autre technologie (on parle de <i>passerelle ou pont</i>). C’est typiquement le cas de la passerelle JDBC-ODBC, le driver se nomme <span class= "red">JdbcOdbcDriver</span> et il est livré avec le JDK. Le pilote convertit les appels de données Java en appels ODBC et les exécute ensuite à l'aide du pilote ODBC.</span></li> <li class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Pilotes de type 2</span></b><span style='font-family:"Times New Roman"'>: Pilotes dits natifs. Il s'agit en fait d'un mélange de pilotes natifs et de pilotes Java. Les appels JDBC sont convertis en appels natifs pour le serveur de bases de données (Oracle, Sybase, ou autres) généralement en C ou en C++.</span></li> </ul> <p class="MsoNormal" style= 'margin-left:14.2pt;text-indent:-14.2pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-family:"Times New Roman"'>La famille de pilotes 100% Java :</span></u></b><span style= 'font-family:"Times New Roman"'>ils interrogent le gestionnaire de base de données avec du code uniquement écrit en Java. Il n’y a aucune installation à effectuer sur le client ce qui permet d’interroger la base de données dans une applet. Dans cette 2<sup>ème</sup> famille, on distingue :</span></p> <ul type="disc"> <li class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Pilotes de type 3</span></b><span style='font-family:"Times New Roman"'>: Pilotes convertissant les appels JDBC pour un middleware présent sur le serveur et qui fait l’interface avec la BD.</span></li> <li class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Pilotes de type 4</span></b><span style='font-family:"Times New Roman"'>: Pilotes convertissant les appels JDBC directement en un protocole réseau directement exploité par le SGBD.</span></li> </ul> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node3" id="node3"></a>2.1.1<span style= 'font:7.0pt "Times New Roman"'> </span> Pilotes non 100% Java</h3> <h3 style='margin-left:18.0pt;text-indent:0cm'> </h3> <h4><a name="node4" id="node4"></a>2.1.1.1<span style= 'font:7.0pt "Times New Roman"'> </span> Connexion via jdbc-odbc (type 1)</h4> <h5 style='margin-left:18.0pt;text-indent:0cm'><a name="node5" id= "node5"></a>a) Chargement du pilote</h5> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut d’abord charger le pilote pour permettre la connexion à la base et le travail sur les données, cela se fait grâce à un appel à la méthode forName (de la classe Class) à laquelle on transmet le nom de la classe du pilote. Le pilote est alors enregistré auprès du DriverManager (gestionnaire de pilotes) :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>try </span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>{</span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> <b>Class.forName(<span class="blue">"sun.jdbc.odbc.JdbcOdbcDriver"</span>);</b></span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB">}</span> </pre> <pre style='border:none; padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">catch(<span class= "red">ClassNotFoundException</span> e) </span> </pre> <pre style='border: none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">{</span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println(<span class= "blue">"JDBC driver not found."</span>);</span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>}</span> </pre> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Quel que soit le pilote qu’on essaie de charger, c’est l’exception ClassNotFoundException qui est levée (c’est à dire déclenchée) en cas d’échec. Il est obligatoire d’intercepter cette exception ou bien de la renvoyer.</span></p> <h5 style='margin-left:18.0pt;text-indent:0cm'><a name="node6" id= "node6"></a>b) Connexion proprement dite</h5> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Nous allons partir de l'exemple d'une base des voyages sous Access. Précisons bien que le SGBD Access ne permet pas de travailler en vrai mode client-serveur mais la façon de procéder est la même, la différence résidera bien sûr dans les performances.</span></p> <p class="Blockquote" style='margin-left:0cm'>Cette base contient les tables suivantes :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style= 'margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="624" style= 'width:467.8pt;border:solid windowtext 1.0pt;background: white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-family:"Times New Roman"'>T_Voyages(<u>NoVoyage,</u> LibVoyage, DateDebut, Duree, NbInscrits,NoDestination)<br /> T_Destinations(<u>NoDestination,</u> LibDestination)</span></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>La connexion à la base se fait via le DSN(data source name) BDVoyage qui s’appuie sur le pilote Microsoft Access Driver.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Pour se connecter à une base, il est nécessaire de déclarer une référence de type Connection et de créer la connexion par un appel à la méthode <i>getConnection</i> de la classe <i>Drivermanager</i> en indiquant la base de données à l'aide d'une chaîne de connexion.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>String cochaine = "jdbc:odbc:Bdvoyage"; // chaîne de connexion avec DSN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>Connection conn = <b>DriverManager.getConnection</b>(cochaine); // on effectue la connexion</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> //grâce à la méthode getConnection de la classe DriverManager </span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Syntaxe générale pour la connexion :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-family: "Times New Roman"'>Connection objetConnection = DriverManager.getConnection(ChaîneDeConnexion,"NomUtilisateur","MotDePasse");</span></b></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Remarques :</span></p> <p class="MsoNormal" style= 'margin-left:38.8pt;text-indent:-18.0pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-family:"Times New Roman"'>l’accès à une base Oracle dans un programme Java peut être effectué via un DSN ODBC (on choisira bien sûr un pilote pour Oracle lors de la création du DSN). Mais dans le cas d’Oracle, il faudra impérativement préciser le nom d’utilisateur et le mot de passe dans l’instruction de connexion. Sur le serveur, il faudra que le listener Oracle ait été lancé. Sur le client, il faudra avoir installé Net8 et le driver odbc pour Oracle devra bien sûr être présent.</span></p> <p class="MsoNormal" style= 'margin-left:38.8pt;text-indent:-18.0pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='font-family:"Times New Roman"'>En cas d’échec de la connexion, c’est l’exception SQLException qui est levée. Il est obligatoire d’intercepter cette exception ou bien de la renvoyer.</span></p> <p class="MsoNormal" style='margin-left:20.8pt'><span style= 'font-family:"Times New Roman"'> </span></p> <h4><a name="node7" id="node7"></a>2.1.1.2<span style= 'font:7.0pt "Times New Roman"'> </span> Connexion via une API native à installer sur le client (type 2)</h4> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Nous illustrons ce paragraphe avec Oracle.</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faudra installer Oracle OCI Driver sur le serveur et installer Net8 sur le client.</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut que le classpath contienne le chemin de la bibliothèque des drivers Oracle donc le classpath sera :</span></p> <p class="MsoNormal" style='text-indent:35.4pt'><span style= 'font-family:"Times New Roman"'>.;\\NomServeur\NomRépPartagé\Ora81\jdbc\lib\classes12.zip</span></p> <p class="MsoNormal" style='text-indent:35.4pt'><span style= 'font-family:"Times New Roman"'>NomRépPartagé est le nom de partage donné au répertoire Oracle sur le serveur.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Le listener devra être démarré sur le serveur.</span></p> <h5 style='margin-left:18.0pt;text-indent:0cm'><a name="node8" id= "node8"></a>a) Chargement du pilote</h5> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut d’abord charger le pilote pour permettre la connexion à la base, ce chargement se fait grâce à un appel au DriverManager (gestionnaire de pilotes) :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB">try </span> </pre> <pre style='border:none; padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">{</span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> <b> Class.forName("oracle.jdbc.driver.OracleDriver");</b></span> </pre> <pre style='border:none;padding:0cm'> <b><span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> // ou DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());</span></b> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB">}</span> </pre> <pre style='border:none; padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">catch(<span class= "red">ClassNotFoundException</span> e) </span> </pre> <pre style='border: none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">{</span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println(<span class= "blue">"JDBC driver not found."</span>);</span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>}</span> </pre> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span></p> </div> <h5 style='margin-left:18.0pt;text-indent:0cm'><a name="node9" id= "node9"></a>b) Connexion proprement dite</h5> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Nous allons partir de l'exemple de la base des employés sous Oracle.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>String cochaine = "<b>jdbc:oracle:oci8:@nomserv</b>"; // nomserv désigne le nom du // service créé sous Net8</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>Connection conn = <b>DriverManager.getConnection</b>(cochaine, "scott", "tiger"); // on effectue</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // la connexion </span></p> </div> <pre> <b> </b> </pre> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <h3><a name="node10" id="node10"></a>2.1.2<span style= 'font:7.0pt "Times New Roman"'> </span> Pilotes 100% Java</h3> <h3 style='margin-left:18.0pt;text-indent:0cm'> </h3> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Nous illustrons ce paragraphe avec Oracle.</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faudra que Oracle Thin Driver soit installé sur le serveur et il faut que le classpath contienne le chemin de la bibliothèque des drivers Oracle donc le classpath sera :</span></p> <p class="MsoNormal" style='text-indent:35.4pt'><span style= 'font-family:"Times New Roman"'>.;\\NomServeur\NomRépPartagé\Ora81\jdbc\lib\classes12.zip</span></p> <p class="MsoNormal" style='text-indent:35.4pt'><span style= 'font-family:"Times New Roman"'>NomRépPartagé est le nom de partage donné au répertoire Oracle sur le serveur.</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Le listener devra être démarré sur le serveur.</span></p> <h4><a name="node11" id="node11"></a>2.1.2.1<span style= 'font:7.0pt "Times New Roman"'> </span> Chargement du pilote</h4> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut d’abord charger le pilote pour permettre la connexion à la base, ce chargement se fait grâce à un appel au DriverManager (gestionnaire de pilotes) :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB">try </span> </pre> <pre style='border:none; padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">{</span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> <b> Class.forName("oracle.jdbc.driver.OracleDriver");</b></span> </pre> <pre style='border:none;padding:0cm'> <b><span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> // ou DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());</span></b> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB">}</span> </pre> <pre style='border:none; padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">catch(<span class= "red">ClassNotFoundException</span> e) </span> </pre> <pre style='border: none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB">{</span> </pre> <pre style='border:none;padding:0cm'> <span lang="EN-GB" style= 'font-size:12.0pt; font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println(<span class= "blue">"JDBC driver not found."</span>);</span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>}</span> </pre></div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h4><a name="node12" id="node12"></a>2.1.2.2<span style= 'font:7.0pt "Times New Roman"'> </span> Connexion proprement dite</h4> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Nous allons partir de l'exemple de la base des employés sous Oracle.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">String cochaine = "jdbc:oracle:thin:@192.168.1.36:1521:btsig"; </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>Connection conn = <b>DriverManager.getConnection</b>(cochaine, "scott", "tiger"); // on effectue</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // la connexion </span></p> </div> <pre> <b> </b> </pre> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>On peut mettre le nom de la machine (sur Internet, il s'agira d'un nom pleinement qualifié) à la place de l’adresse IP.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <h1><a name="node13" id="node13"></a>3<span style= 'font:7.0pt "Times New Roman"'> </span> Le client-serveur de données</h1> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Quand la connexion est établie (si aucune exception n'a été interceptée) on peut écrire des requêtes pour construire, interroger ou mettre à jour la base.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Plusieurs interfaces sont utilisées :</span></p> <p class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>Statement</span></u></b> <b><span style='font-family:"Times New Roman"'>:</span></b> <span style='font-family: "Times New Roman"'>C'est une interface que l'application emploie pour transmettre des requêtes SQL à la base.</span></p> <p class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>ResultSet :</span></u></b> <span style='font-family:"Times New Roman"'>un ResultSet sera créé lors de l’exécution d’une requête d’interrogation, c’est l’ensemble des tuples du résultat de la requête, cet ensemble pourra bien sûr être manipulé.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>En cas d’échec de l’une des méthodes permettant de travailler sur les données, c’est l’exception SQLException qui est levée. Il est obligatoire d’intercepter cette exception ou bien de la renvoyer. C'est l'exception SQLException qui est levée en cas d'erreur pour chacune des opérations vues dans la suite de ce cours.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h2><a name="node14" id="node14"></a>3.1<span style= 'font:7.0pt "Times New Roman"'> </span> Interrogation de la base</h2> <p class="MsoNormal"><b><i><span style= 'font-family:"Times New Roman"'> </span></i></b></p> <h3><a name="node15" id="node15"></a>3.1.1<span style= 'font:7.0pt "Times New Roman"'> </span> Requête et curseur (ResultSet)</h3> <p class="MsoNormal" style='margin-left:35.25pt'><b><i><span style= 'font-family: "Times New Roman"'> </span></i></b></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut commencer par créer un Statement, il s’appuie sur la connexion créée précédemment :</span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style='margin-left:.25pt;border-collapse:collapse'> <tr> <td width="482" style= 'width:361.5pt;border:solid windowtext 1.0pt;background: white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b><b><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">Statement</span></b> <span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">requete = conn.<b>createStatement</b>();</span></p> </td> </tr> <tr> <td width="482" style= 'width:361.5pt;background:white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> </td> </tr> </table> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>executeQuery exécute la requête et place le résultat dans un ResultSet. Cet objet (nommé resultat dans l’exemple ci-dessous) permet d'accéder aux données du résultat de la requête :</span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style= 'margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="605" style= 'width:16.0cm;border:solid windowtext 1.0pt;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> <b> </b></span><b><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">ResultSet</span></b> <span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">resultat = requete.<b>executeQuery</b> ("select empno, ename, sal, hiredate from emp");</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> </td> </tr> </table> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Après la requête, le curseur est positionné juste avant la première ligne du résultat mais le 1<sup>er</sup> enregistrement n’est pas accessible, il faut effectuer un <b>next()</b> comme par la suite pour avancer d’enregistrement en enregistrement.</span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" style='margin-left:.25pt;border-collapse:collapse'> <tr> <td width="227" style= 'width:6.0cm;border:solid windowtext 1.0pt;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> resultat.next();</span></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><b><i><span style= 'font-family:"Times New Roman"'>La fin du ResultSet</span></i></b> <span style='font-family:"Times New Roman"'>est détectée par le renvoi de la valeur false.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut donc effectuer une lecture initiale, puis prévoir une boucle incluant l’utilisation des données et la lecture du suivant.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">boolean bool=resultat.next();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">if (bool)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ...</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ...</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> bool=resultat.next(); }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (bool) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>else</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> …</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'>Facilité d’écriture :</span></u></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>while (resultat.next()) et if (resultat.next()) assurent la lecture de l’enregistrement et la détection de la fin du ResultSet.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">if (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ...</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ...</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (resultat.next()) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>else</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> …</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Si on ne souhaite pas distinguer le cas ResultSet vide, on utilisera un TANTQUE et la structure du traitement sera la suivante :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">while (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> ...</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> ... </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node16" id="node16"></a>3.1.2<span style= 'font:7.0pt "Times New Roman"'> </span> Récupération des données</h3> <p class="MsoNormal"><b><i><span style= 'font-family:"Times New Roman"'> </span></i></b></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Pour récupérer les données des différents champs, il faut utiliser la méthode getObject (qui permet de récupérer les données sans se soucier de leurs types mais attention, cela ne fonctionne généralement qu’avec le pont JDBC/ODBC et à la condition qu’il ne s’agisse pas de données de type réel ou date ou heure) ou utiliser les méthodes adaptées aux types des données à récupérer mais alors il faut connaître le type des données récupérées.</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>De plus, si on utilise getObject, la donnée récupérée ne sera pas « typée » et il sera par exemple impossible d’effectuer des opérations arithmétiques sur cette donnée à moins de la convertir.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Attention</span></b> <span style= 'font-family:"Times New Roman"'>les colonnes sont numérotées à partir de 1.</span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Principales méthodes de récupération des champs suivant le type:</span></b></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Type SQL</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Type Java</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Méthode de récupération</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">CHAR, VARCHAR, VARCHAR2</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">String</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span><span style= 'font-family:"Times New Roman"'>getString(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>NUMERIC (NUMBER sous ORACLE) sans partie décimale</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>int ou long ou java.math.BigDecimal</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getInt(Numcolonne) ou getLong(NumColonne) ou getBigDecimal(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>NUMERIC (NUMBER sous ORACLE) avec partie décimale</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>double ou float</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getDouble(Numcolonne) ou</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getFloat(NumColonne) ou</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">getBigDecimal(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">SMALLINT</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">short</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">getShort(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>INTEGER</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>int</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getInt(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>DATE</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Java.sql.Date</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getDate(NumColonne)</span></p> </td> </tr> <tr> <td width="203" valign="top" style= 'width:152.35pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>TIME ou partie heure de DATE</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>java.sql.Time</span></p> </td> <td width="203" valign="top" style= 'width:152.35pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>getTime(NumColonne) </span></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'>Remarques :</span></u></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>. Ces méthodes sont surchargées pour permettre la transmission du nom de la colonne à la place de son numéro.</span></p> <table class="MsoNormalTable" border="0" cellspacing="0" cellpadding="0" width="614" style= 'width:460.7pt;margin-left:.4pt;border-collapse:collapse'> <tr> <td width="614" style='width:460.7pt;padding:0cm 0cm 0cm 0cm'> <span style='font-size:12.0pt;font-family:Arial'><br clear="all" style='page-break-before: always' /></span> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoHeader"><u>Récupération et affichage des colonnes issues de la requête précédente :</u></p> </td> </tr> <tr> <td width="614" style='width:460.7pt;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>System.out.println("Numéro employé : " + resultat.getObject(1));</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>System.out.println("Nom : "+" " + resultat.getObject(2));</span></p> <p class="MsoHeader">System.out.println("Sal : "+" " + resultat.getFloat("sal"));</p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>System.out.println("Date d'embauche : "+" " + resultat.getDate(</span>"<span style= 'font-family:"Times New Roman"'>hiredate</span>"<span style= 'font-family:"Times New Roman"'> ));</span></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node17" id="node17"></a>3.1.3<span style= 'font:7.0pt "Times New Roman"'> </span> Exemple global</h3> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'>Version avec renvoi des exceptions :</span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class ExGlobal</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">public static void main (String args[]) throws ClassNotFoundException,</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SQLException</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> String cochaine="jdbc:odbc:ora8";</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = DriverManager.getConnection(cochaine,"scott","tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Statement requete = conn.createStatement(); </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSet resultat = requete.executeQuery("SELECT</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> empno,ename,sal,hiredate FROM EMP");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>if (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println("Numéro Nom Salaire Date d'embauche");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // Si on n’avait pas utilisé le pont JDBC/ODBC pour la</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // connexion, on n’aurait pas pu utiliser getObject. </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">System.out.println(resultat.getObject(1)+"\t"+</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getObject(2) + "\t" + resultat.getFloat("sal")+ "\t" +</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getDate(</span><span lang="EN-GB" xml:lang= "EN-GB">"</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">hiredate</span><span lang="EN-GB" xml:lang= "EN-GB">")</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> while (resultat.next()) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> else</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println (“La table des employés est vide”);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> resultat.<b>close() ;</b> // Libération des ressources occupées par l’objet resultat</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> requete.<b>close() </b>; // Libération des ressources occupées par l’objet requete</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> conn.<b>close()</b>; // Libération des ressources occupées par l’objet conn</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'><span style= 'text-decoration:none'> </span></span></u></p> <p class="MsoNormal"><u><span style= 'font-family:"Times New Roman"'>Version avec interception des exceptions :</span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 3.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class ExGlobal</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">public static void main (String args[])</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> try</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> { </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> String cochaine="jdbc:odbc:ora8";</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> DriverManager.getConnection(cochaine,"scott","tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Statement requete = conn.createStatement(); </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename,sal,hiredate FROM EMP");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>if (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println ("Numéro Nom Salaire Date d'emb.");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // Si on n’avait pas utilisé le pont JDBC/ODBC pour la</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // connexion, on n’aurait pas pu utiliser getObject. </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">System.out.println(resultat.getObject(1)+"\t"+</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getObject(2) + "\t" + resultat.getFloat("sal")+ "\t"</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> + resultat.getDate(4));</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (resultat.next()) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> else</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println (“La table des employés est vide”);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.close() ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> requete.close() ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> conn.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> catch(SQLException e)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println("Erreur\n" + e.getMessage()); // e contient le nom </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // de l’exception et le message d’erreur, e.getMessage() renvoie</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // uniquement le message d’erreur</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> e.printStackTrace(); // Affichage de la pile des appels de méthodes</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> } </span></p> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family: "Times New Roman"'> catch(<span class="red">ClassNotFoundException</span> e) </span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> {</span> </pre> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> System.out.println(<span class="blue">"Le pilote JDBC n'a pas été trouvé"</span>);</span> </pre> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> e.printStackTrace(); // Affichage de la pile des appels de méthodes</span></p> <pre style='border:none;padding:0cm'> <span style= 'font-size:12.0pt;font-family: "Times New Roman"'> }</span> </pre> <pre style='border: none;padding:0cm'> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> }</span> </pre> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>} </span></p> </div> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <h3><a name="node18" id="node18"></a>3.1.4<span style= 'font:7.0pt "Times New Roman"'> </span> Méta-données</h3> <p>Il est possible de récupérer des informations sur la base (nom du SGBD, nom des tables, des index…, nom de l’utilisateur courant…) grâce à l’interface <b>DataBaseMetaData</b> et des informations sur un ResultSet (nombre de colonnes, nom, type… des colonnes) grâce à l’interface <b>ResultSetMetaData.</b></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal" style='display:none'><b><u><span style= 'font-family:"Times New Roman"; display:none'>DataBaseMetaData :</span></u></b></li> </ul> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman";display:none'> </span></b></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman";display:none'>DatabaseMetaData</span></b> <span style='font-family:"Times New Roman";display:none'>dbmd = conn.<b>getMetaData()</b>; // retourne les méta-données de la base</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"; display:none' xml:lang= "EN-GB">System.out.println(dbmd.<b>getDatabaseProductName()</b>);</span></p> <p class="MsoNormal"><span lang="DE" style= 'font-family:"Times New Roman"; display:none' xml:lang= "DE">System.out.println(dbmd.<b>getUserName()</b>);</span></p> <p class="MsoHeader"><span lang="DE" xml:lang= "DE"> </span></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>ResultSetMetaData :</span></u></b></li> </ul> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Cette interface fournit des informations sur un ResultSet, comme :</span></p> <ul type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>nombre de colonnes : getColumnCount()</span></li> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>nom d’une colonne : getColumnName(NuméroColonne)</span></li> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>label d’une colonne : getColumnLabel(NuméroColonne)</span></li> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>type d’une colonne : getColumnType(NuméroColonne)</span></li> </ul> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-family:"Times New Roman"'>Pour le type, le problème est que pour NUMERIC (ou NUMBER sous Oracle), la méthode renvoie 3 qu’il y ait une partie décimale ou non.</span></p> <ul type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>etc.</span></li> </ul> <pre> <span class="red"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>Exemple : on reprend l’exemple précédent en utilisant l’interface </span></span><span style='font-size:12.0pt;font-family:"Times New Roman"'>ResultSetMetaData pour l’affichage de l’en-tête.</span> </pre> <pre> <span class="red"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></span> </pre> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">if (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> <b>ResultSetMetaData</b> rsmd;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> rsmd = resultat.<b>getMetaData()</b>;</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">for (int i=1;i<=rsmd.<b>getColumnCount()</b>;i++)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.print(rsmd.<b>getColumnName(i)</b> + "\t");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println("");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println(resultat.getObject(1)+"\t"+ resultat.getObject(2) +</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> "\t" + resultat.getFloat("sal")+ "\t" + resultat.getDate(</span><span lang="EN-GB" xml:lang= "EN-GB">"</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">hiredate</span><span lang="EN-GB" xml:lang= "EN-GB">"</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">));</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (resultat.next()) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>else</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span style= 'font-family:"Times New Roman"'>System.out.println (“La table des employés est vide”);</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node19" id="node19"></a>3.1.5<span style= 'font:7.0pt "Times New Roman"'> </span> Requête paramétrée</h3> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il est tout à fait possible de concaténer des parties fixes et des parties variables pour constituer le texte de la requête.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Exemple avec une date :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>String demb = "02-04-1981";</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">("SELECT empno,ename, sal FROM EMP where hiredate >= '" + demb + "'");</span></p> </div> <p class="MsoNormal"><b><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></b></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>Exemple avec un entier :</span></b></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>int dept = 10; </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename,sal,hiredate FROM EMP where deptno = " + dept);</span></p> </div> <p class="MsoHeader"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <h3><a name="node20" id="node20"></a>3.1.6<span style= 'font:7.0pt "Times New Roman"'> </span> Requête précompilée</h3> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <p class="MsoHeader"> Il faut pour cela utiliser l’interface <b>PreparedStatement</b> à laquelle on associe une requête et un type de curseur (curseur en lecture seule et sans positionnement par défaut).</p> <p class="MsoHeader">L’interface PreparedStatement dérive de Statement.</p> <p class="MsoHeader">La requête pourra être paramétrée.</p> <p class="MsoHeader"> </p> <p class="MsoHeader"><b><u>Deux ou trois étapes pour obtenir le ResultSet :</u></b></p> <p class="MsoHeader"><b><u><span style= 'text-decoration:none'> </span></u></b></p> <p class="MsoHeader" style= 'margin-left:36.0pt;text-indent:-18.0pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> Préparation de la requête :</p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman";layout-grid-mode:line' xml:lang="EN-GB">PreparedStatement pstmt = conn.prepareStatement</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman";layout-grid-mode:line' xml:lang= "EN-GB"> ("select empno,ename from emp where ename = ?");</span></p> </div> <p class="MsoHeader"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoHeader">? désigne l'emplacement d'un paramètre.</p> <p class="MsoHeader"> </p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Alimentation des paramètres (si requête paramétrée) :</span></li> </ul> <p class="MsoHeader">Chaque paramètre doit être renseigné en appliquant une méthode</p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>set<i>Type</i> (NuméroParamètre</span></b><span style= 'font-family:"Times New Roman"'>, Valeur) sur l’objet PreparedStatement.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman";layout-grid-mode: line'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman";layout-grid-mode:line'>pstmt.setString(1,"MILLER"); // renseignement du 1<sup>er</sup> et seul paramètre avec la</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman";layout-grid-mode:line'> // valeur MILLER</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Les principales méthodes set<i>Type</i> sont :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setString(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setInt(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setLong(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setFloat(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setDouble(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>setDate(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Exécution de la requête :</span></li> </ul> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman";layout-grid-mode:line' xml:lang="EN-GB">ResultSet rs = pstmt.executeQuery();</span></p> </div> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Remarque : si on souhaite exécuter plusieurs fois la requête, il suffira d’appliquer uniquement la méthode executeQuery à l’objet PreparedStatement, il n’y pas à nouveau compilation de la requête. Ceci est vrai même pour une requête paramétrée car en fait la requête est précompilée sans les valeurs des paramètres. Bien sûr, si on souhaite modifier la valeur des paramètres, il faudra à nouveau utiliser les méthodes set<i>Type</i>.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>import java.io.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class Reqpar</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> public static void main (String args[]) throws ClassNotFoundException,</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SQLException, IOException</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName("oracle.jdbc.driver.OracleDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = DriverManager.getConnection</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("jdbc:oracle:thin:@192.168.10.36:1521:btsig", "scott", "tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> PreparedStatement pstmt = conn.prepareStatement</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("select empno,ename,sal,job from emp where deptno = ?");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> pstmt.setInt (1,Entrée.entier("\nSaisir le n° de département"));</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // Si le numéro saisi n’existe pas, l’en-tête seule sera affichée</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println ("Numéro Nom Salaire Date d'embauche");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">ResultSet rs = pstmt.executeQuery();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (rs.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println (rs.getInt(1)+" " + rs.getString(2)+</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> " " + rs.getFloat(3) + " " + rs.getString(4));</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> rs.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> while (Entrée.car("\nAutre département(O/N)")=='O');</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">pstmt.close() ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> conn.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style= 'font-family:"Times New Roman"'>} </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node21" id="node21"></a>3.1.7<span style= 'font:7.0pt "Times New Roman"'> </span> Traitement avancé des ResultSet</h3> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il est possible de déclarer un ResultSet sur lequel on peut effectuer les déplacements et positionnements que l’on veut, on parle alors de ResultSet navigable.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Jusqu’à maintenant, nous avons créé un Statement sans préciser de paramètres, le ResultSet est alors uniquement en consultation et peut être uniquement parcouru du début à la fin (pas de retour ni de positionnement possible), il est dit non navigable.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il est possible de préciser 2 paramètres :</span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'>1<sup>er</sup> paramètre :</span></b><span style= 'font-family:"Times New Roman"'>navigabilité et visibilité des mises à jour des autres :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> <span style='layout-grid-mode:line'>ResultSet.TYPE_FORWARD_ONLY : non navigable et on ne voit pas les mises à jour des autres</span></span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> <span style= 'layout-grid-mode:line'>ResultSet.TYPE_SCROLL_INSENSITIVE : navigable et on ne voit pas les mises à jour des autres</span></span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman";layout-grid-mode: line'> ResultSet.TYPE_SCROLL_SENSITIVE : navigable et on voit les mises à jour des autres.</span></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman";layout-grid-mode: line'> </span></b></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman";layout-grid-mode: line'>2ème paramètre :</span></b> <span style= 'font-family:"Times New Roman"; layout-grid-mode:line'>lecture seule ou mise à jour :</span></p> <p class="MsoNormal" style='margin-left:35.4pt;text-indent:35.4pt'> <span lang="EN-GB" style= 'font-family:"Times New Roman";layout-grid-mode:line' xml:lang= "EN-GB">ResultSet.CONCUR_READ_ONLY</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> <span style= 'layout-grid-mode:line'>ResultSet.CONCUR_UPDATABLE</span> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Remarque : les 2 paramètres qui viennent d’être étudiés pour le createStatement peuvent être mis en 2<sup>ème</sup> et 3<sup>ème</sup> paramètres du preparedStatement.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <pre> <b><u><span style= 'font-size:12.0pt;font-family:"Times New Roman"; color:black'>Méthodes pour les déplacements en plus de la méthode next():</span></u></b> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>public void afterLast() // Positionne le pointeur après le dernier ergt</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>public void beforeFirst() // Positionne le pointeur avant le premier ergt</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>public boolean previous() // Positionne le pointeur sur l’ergt précédent et retourne true si OK </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> // et false sinon</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>public boolean first() // Déplace le pointeur sur le 1<sup>er</sup> ergt et retourne true si OK et false</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> // sinon </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'>public boolean last() // Déplace le pointeur sur le dernier ergt et retourne true si OK et </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'> // false sinon </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>public boolean relative(int nombre_enregistrements) // Déplace le pointeur de </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> nombre_enregistrements plus loin ou recule de nombre_enregistrements (si</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> valeur négative). Il faut déjà être positionné sur un ergt</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>public boolean absolute(int position) // Déplace le pointeur sur l’enregistrement </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> // indiqué (la position est numérotée à partir de 1). Ne nécessite </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> // pas de positionnement préalable. </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <p class="MsoNormal"><u><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">Exemple :</span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:31.0pt 31.0pt 31.0pt 31.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class navigation</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> public static void main (String args[]) throws ClassNotFoundException,</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SQLException</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName("oracle.jdbc.driver.OracleDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = DriverManager.getConnection</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("jdbc:oracle:thin:@192.168.10.36:1521:btsig", "scott", "tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Statement requete = conn.createStatement</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> (ResultSet.TYPE_SCROLL_INSENSITIVE,</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSet.CONCUR_READ_ONLY);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename, sal,hiredate FROM EMP");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.<b>afterLast()</b>;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> if (resultat.<b>previous()</b>)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSetMetaData rsmd;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> rsmd = resultat.getMetaData();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> for (int i=1;i<=rsmd.getColumnCount();i++)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.print(rsmd.getColumnLabel(i) + "\t");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println("");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> System.out.println(resultat.getInt(1)+"\t"+ </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getString(2) + "\t" + resultat.getFloat("sal")+ "\t"</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> + resultat.getDate(4));</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> while (<b>resultat.previous()</b>) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>// Après avoir traité le ResultSet d'arrière en avant, on peut très bien</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // aller au début et le traiter du début à la fin</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.<b>beforeFirst()</b>;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.<b>next()</b>;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> // …</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> requete.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style= 'font-family:"Times New Roman"'>conn.close();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <h2><a name="node22" id="node22"></a>3.2<span style= 'font:7.0pt "Times New Roman"'> </span> Mise à jour (au sens large) de la base de données</h2> <p class="MsoNormal"> </p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman";layout-grid-mode: line'> </span></b> <b><span style='font-family:"Times New Roman"'>La mise à jour d'une base de données peut être effectuée :</span></b></p> <p class="MsoNormal"><b><span style= 'font-family:"Times New Roman"'> </span></b></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>par le biais d'une requête SQL de type UPDATE, INSERT ou DELETE exécutée avec la méthode <b>executeUpdate</b>("Requête") de la classe Statement. Le résultat renvoyé par l'exécution de la requête indiquera le nombre de lignes mises à jour dans la base, contrairement à une requête de sélection qui renvoie un ResultSet.</span></li> </ul> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>ou</span></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><span style= 'font-family:"Times New Roman"'>avec un ResultSet modifiable : il faut déclarer le Statement avec CONCUR_UPDATABLE. On parle alors de ResultSet modifiable. Attention, il ne faut pas utiliser le pont JDBC-ODBC pour traiter un ResultSet modifiable s’il s’agit d’une base Oracle.</span></li> </ul> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node23" id="node23"></a><span style= 'layout-grid-mode:line'>3.2.1<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='layout-grid-mode:line'>Ajout</span></h3> <p class="MsoNormal"><b><i><span style= 'font-family:"Times New Roman";layout-grid-mode: line'> </span></i></b></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-family:"Times New Roman"'>En utilisant une requête SQL</span></b></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Exemple :</span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style= 'margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="614" style= 'width:460.7pt;border:solid windowtext 1.0pt;background: white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">Statement st = conn.createStatement();<br /> st.<b>executeUpdate</b>("INSERT INTO dept values (50, ‘INFORMATIQUE’, ‘BATIMENT C’") ;<br /> <br /></span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>// Si on effectue l’INSERT avec SELECT au lieu de VALUES alors il est possible d’insérer</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>// plusieurs lignes à la fois et il peut être intéressant de récupérer le nombre de lignes insérées</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">int nbIns = st.<b>executeUpdate</b>("INSERT INTO dept SELECT …") ;</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>System.out.println (nbIns + " ligne(s) insérée(s)");</span></p> </td> </tr> </table> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-size:12.0pt;font-family:"Times New Roman"'>En utilisant un curseur</span></b></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">Exemple :</span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style= 'margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="586" style= 'width:439.45pt;border:solid windowtext 1.0pt;background: white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename, sal,hiredate, deptno FROM EMP");</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>resultat.<b>moveToInsertRow()</b>; // On se met en insertion</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.<b>updateInt</b>(1,7000);</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.<b>updateString</b>(2,"TITI");</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.<b>updateFloat</b>("sal",100);</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>resultat.<b>updateInt</b>(5,30);</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>resultat.<b>insertRow()</b>; // On ajoute la ligne dans la table</span></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Les principales méthodes update<i>Type</i> sont :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateString(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateInt(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateLong(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateFloat(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateDouble(NumColonne, Valeur)</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>updateDate(NumColonne, Valeur)</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Remarque :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Ainsi qu'on peut le voir dans l'exemple, ces méthodes sont surchargées pour permettre la transmission du nom de la colonne à la place de son numéro.</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <h3><a name="node24" id="node24"></a>3.2.2<span style= 'font:7.0pt "Times New Roman"'> </span> Modification</h3> <p class="MsoBodyText" style='text-indent:35.4pt'> <b><i><span style='font-size: 12.0pt;font-family:"Times New Roman"'> </span></i></b></p> <p class="MsoBodyText" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-size:12.0pt;font-family:"Times New Roman"'>En utilisant une requête SQL</span></b></p> <p class="MsoBodyText"><b><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></b></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>Exemple :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style= 'font-family:"Times New Roman";color:black;layout-grid-mode:line' xml:lang="EN-GB">st.executeUpdate("UPDATE emp set sal = sal * 1.05”); // st est un Statement</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style= 'font-family:"Times New Roman";color:black;layout-grid-mode:line' xml:lang="EN-GB"> </span></p> </div> <b><span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB"><br clear="all" style= 'page-break-before:always' /></span></b> <p class="MsoBodyText"><b><span lang="EN-GB" style= 'font-size:12.0pt;font-family: "Times New Roman"' xml:lang= "EN-GB"> </span></b></p> <p class="MsoBodyText" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-size:12.0pt;font-family:"Times New Roman"'>En utilisant un curseur</span></b></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>On est positionné sur un enregistrement, on effectue la modification des champs puis on effectue la modification de l’enregistrement.</span></p> <p class="MsoBodyText"><b><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></b></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">Exemple :</span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="614" style= 'width:460.7pt;margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="614" style= 'width:460.7pt;border:solid windowtext 1.0pt;border-bottom: none;background:white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename, sal,hiredate FROM EMP");</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">if (resultat.next())</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">{</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> do</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoHeader"><span lang="EN-GB" xml:lang= "EN-GB"> System.out.println (resultat.getInt(1)+"\t"+ resultat.getString(2) +</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>"\t" + resultat.getFloat("sal")+ "\t" + resultat.getDate(4));</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> float nouvsal = Entrée.flottant</span></p> <p class="MsoHeader"> ("Employé " + resultat.getInt(1) + ", saisir le nouveau salaire : ");</p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">resultat.<b>updateFloa</b>t(3, nouvsal); </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.<b>updateRow</b>();</span></p> <p class="MsoHeader"><span lang="EN-GB" xml:lang= "EN-GB"> }</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> while (resultat.next()) ;</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>}</span></p> </td> </tr> <tr> <td width="614" style= 'width:460.7pt;border:solid windowtext 1.0pt;border-top: none;background:white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> </td> </tr> </table> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <h3><a name="node25" id="node25"></a>3.2.3<span style= 'font:7.0pt "Times New Roman"'> </span> Suppression</h3> <p class="MsoBodyText" style='text-indent:18.0pt'><span style= 'font-size:12.0pt; font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText" style= 'margin-left:36.0pt;text-indent:-18.0pt'><span style= 'font-size:12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-size:12.0pt;font-family:"Times New Roman"'>Par une requête SQL</span></b></p> <p class="MsoBodyText"><b><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></b></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style= 'font-family:"Times New Roman";color:black;layout-grid-mode:line' xml:lang="EN-GB">st.executeUpdate("DELETE FROM emp where ename = 'MILLER'");</span></p> </div> <p class="MsoBodyText"><span lang="EN-GB" style= 'font-size:12.0pt;font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoBodyText" style= 'margin-left:36.0pt;text-indent:-18.0pt'><span style= 'font-size:12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><span style='font-size:12.0pt;font-family:"Times New Roman"'>En utilisant un curseur</span></b></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>L'exemple qui suit montre la suppression de la ligne 5 d'un ResultSet.</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style= 'margin-left:.25pt;border-collapse:collapse;border:none'> <tr> <td width="586" style= 'width:439.45pt;border:solid windowtext 1.0pt;background: white;padding:0cm 0cm 0cm 0cm'> <p class="MsoNormal"> </p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">ResultSet resultat = requete.executeQuery</span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> ("SELECT empno,ename, sal,hiredate FROM EMP");</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>resultat.absolute(5); <i> </i><br /> resultat.<b>deleteRow</b>(); // Suppression de la ligne en cours</span></p> </td> </tr> </table> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <h3><a name="node26" id="node26"></a><span style= 'layout-grid-mode:line'>3.2.4<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='layout-grid-mode:line'>Requête de mise à jour (au sens large) paramétrée</span></h3> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>C’est le même principe que pour un SELECT c’est-à-dire que la requête INSERT, UPDATE ou DELETE peut contenir des parties fixes et des parties variables.</span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <h3><a name="node27" id="node27"></a><span style= 'layout-grid-mode:line'>3.2.5<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='layout-grid-mode:line'>Requête de mise à jour (au sens large) précompilée</span></h3> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Cela s’effectue avec un objet PreparedStatement comme pour une requête de consultation (excepté que l’exécution sera effectuée avec executeUpdate). La requête pourra être paramétrée ou non.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Exemple :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">import java.util.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class maj2</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> public static void main (String args[]) throws ClassNotFoundException,</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">SQLException</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> String url="jdbc:odbc:micor";</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = DriverManager.getConnection(url,"scott","tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> java.sql.Date dsys = new java.sql.Date(System.currentTimeMillis());</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> PreparedStatement pstmt = conn.prepareStatement</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ("insert into emp values (3,'TOTO','nn',7000,?,10,10,10)");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> pstmt.setDate(1,dsys); </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> pstmt.executeUpdate(); </span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span style= 'font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <b><span style= 'font-size:10.0pt;font-family:"Times New Roman";color:navy'><br clear="all" style='page-break-before:always' /></span></b> <p class="chap" style='margin-right:-14.5pt;border:none'> <span style='color:windowtext'>DEVELOPPEMENT CLIENT-SERVEUR EN JAVA (suite)</span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'> <u><span style='font-size: 12.0pt'>Bref rappel de la 1<sup>ère</sup> partie à partir d’un exemple :</span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">import java.sql.*;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">class ExGlobal</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">{</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">public static void main (String args[]) throws ClassNotFoundException,</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SQLException</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> String cochaine="jdbc:odbc:ora8";</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Connection conn = DriverManager.getConnection(cochaine,"scott","tiger");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Statement st = conn.createStatement(); </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> ResultSet resultat = st.executeQuery("SELECT empno, ename, sal,</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> hiredate FROM EMP");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> if (resultat.next())</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>{</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println("Numéro Nom Salaire Date d'embauche");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> do</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> {</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // Si on n’avait pas utilisé le pont JDBC/ODBC pour la</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // connexion, on n’aurait pas pu utiliser getObject. </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span> <span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">System.out.println(resultat.getObject(1)+"\t"+</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getObject(2) + "\t" + resultat.getFloat("sal")+ "\t" +</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> resultat.getDate(</span><span lang="EN-GB" xml:lang= "EN-GB">"</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">hiredate</span><span lang="EN-GB" xml:lang= "EN-GB">")</span><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>}</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> while (resultat.next()) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> else</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> System.out.println (“La table des employés est vide”);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> resultat.<b>close() ;</b> // Libération des ressources occupées par l’objet resultat</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> st.<b>close() </b>; // Libération des ressources occupées par l’objet st</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> conn.<b>close()</b>; // Libération des ressources occupées par l’objet conn</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> }</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>}</span></p> </div> <p class="chap" style='margin-left:14.2pt;border:none'> <u><span style='font-size: 12.0pt'><span style= 'text-decoration:none'> </span></span></u></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="chap" style='margin-left:14.2pt;border:none'><span style= 'font-size: 12.0pt'> </span></p> <p class="MsoBodyText"><span style= 'font-size:12.0pt;font-family:"Times New Roman"'> </span></p> <h1><a name="node28" id="node28"></a><span style= 'layout-grid-mode:line'>4<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='layout-grid-mode:line'>Les requêtes de définition ou de contrôle des données</span></h1> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Avec la méthode executeUpdate, on peut également effectuer toute requête du LDD</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>(Exemple : create table) ou du LCD (Exemple : grant).</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">Exemple :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">Statement st = conn.createStatement(); </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">st.executeUpdate ("grant all on emp to public");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> </div> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <h2 style='margin-left:18.0pt;text-indent:0cm'><a name="node29" id= "node29"></a><span style='layout-grid-mode: line'>4.1<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span style='layout-grid-mode:line'>La validation/annulation des mises à jour (au sens large)</span><span style= 'layout-grid-mode:line'>effectuées</span></h2> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Avec JDBC, par défaut, un commit est effectué après chaque mise à jour. Si on ne souhaite pas le mode par défaut, il faut passer en mode manuel :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>conn.<b>setAutocommit(false)</b>;</span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>et ensuite, il faudra se charger du commit ou rollback :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>conn.<b>commit()</b>;</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>ou</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>conn.<b>rollback()</b>;</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>On peut repasser à tout moment en mode autocommit :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>conn.<b>setAutocommit(true)</b> ;</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>Il faut préciser que par défaut (mode autocommit) : en cas de plantage, les mises à jour effectuées ne sont pas « défaites » par jdbc ; ceci est dû au fait qu’un commit est effectué après chaque instruction.</span></p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <h1><a name="node30" id="node30"></a>5<span style= 'font:7.0pt "Times New Roman"'> </span> Le client-serveur de traitements</h1> <pre> <span style='color:black'> </span> </pre> <h2><a name="node31" id="node31"></a>5.1<span style= 'font:7.0pt "Times New Roman"'> </span> L'exécution de procédures stockées</h2> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>On utilise pour cela l'interface CallableStatement qui dérive de PreparedStatement.</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Bien sûr, une procédure stockée peut être paramétrée.</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Il faut d'abord préparer l'appel puis valoriser les paramètres (si procédure paramétrée) puis exécuter l'appel.</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <h3><a name="node32" id="node32"></a>5.1.1<span style= 'font:7.0pt "Times New Roman"'> </span> Syntaxe</h3> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre style='margin-left:36.0pt;text-indent:-18.0pt'> <span style='font-size:12.0pt; font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span><b><u><span style='font-size:12.0pt;font-family:"Times New Roman";color:black'>Préparation :</span></u></b> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Sans paramètres :</span> </pre> <p class="MsoNormal"><b><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement</span></b> <span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">cStmt = conn.<b>prepareCall</b>("{call nom_Procédure}");</span></p> <p class="MsoNormal"><b><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></b></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Avec paramètres :</span></p> <pre> <b><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>CallableStatement</span></b><span style='font-size:12.0pt;font-family:"Times New Roman"'> cStmt = conn.<b>prepareCall</b>("<span style='color:black'>{call nom_Procedure (?, ?, ...)}");</span></span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Il faut ensuite valoriser les paramètres au moyen des méthodes set<i>Type</i> (setInt …). Si les paramètres sont uniquement en entrée, on peut indiquer leurs valeurs directement dans l'appel à la méthode prepareCall.</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre style='margin-left:36.0pt;text-indent:-18.0pt'> <span style='font-size:12.0pt; font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span><b><u><span style='font-size:12.0pt;font-family:"Times New Roman";color:black'>Exécution :</span></u></b> </pre> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>cStmt.executeUpdate();</span></p> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <h3><a name="node33" id="node33"></a>5.1.2<span style= 'font:7.0pt "Times New Roman"'> </span> Exemples</h3> <p class="MsoNormal"> </p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>Procédure non paramétrée</span></u></b></li> </ul> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement cStmt =conn.prepareCall("{call majsal}");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.executeUpdate();</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoHeader"> </p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Code PL/SQL de la procédure :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CREATE PROCEDURE majsal</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> DECLARE NumSal int;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> DECLARE montant float;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SET NumSal = 7934;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> SET montant = 100;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Update emp set sal = sal + montant</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> Where empno = NumSal ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">END majsal ;</span></p> </div> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>Procédure avec paramètres en entrée</span></u></b></li> </ul> <p class="MsoNormal" style='margin-left:18.0pt'><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement cStmt =conn.prepareCall("{call majsal2(?,?)}");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">cStmt.setInt(1,7934);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">cStmt.setFloat(2,5000.5F);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.executeUpdate();</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style='font-family:"Times New Roman"'>On pouvait également transmettre directement les valeurs des paramètres à la méthode prepareCall :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement cStmt =conn.prepareCall("{call majsal2(7934,5000.5F)}");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.executeUpdate();</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Code PL/SQL de la procédure :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">create procedure majsal2(x_empno int, x_sal float)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> UPDATE EMP SET sal = sal + x_sal WHERE empno = x_empno;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>END majsal2;</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <ul style='margin-top:0cm' type="disc"> <li class="MsoNormal"><b><u><span style= 'font-family:"Times New Roman"'>Procédure avec paramètres en entrée et en entrée-sortie</span></u></b></li> </ul> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement cStmt =</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">conn.prepareCall("{call majsal3(?,?)}");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.setInt(1,7934);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.setFloat(2,4000);</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.registerOutParameter(2, Types.FLOAT); // Obligatoire pour les paramètres en</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> // e/s ou en sortie</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.executeUpdate();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>System.out.println("Nouveau salaire : " + cStmt.getFloat(2));</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Code PL/SQL de la procédure :</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">create or replace procedure majsal3(x_empno in int, x_sal in out float)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">BEGIN</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> UPDATE EMP SET sal = sal + x_sal WHERE empno = x_empno;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> select sal into x_sal from emp where empno = x_empno;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>END majsal3;</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Remarques : le principe pour les paramètres en sortie est le même que pour les paramètres en e/s excepté qu’il n’y a pas d’instruction set<i>Type</i> pour alimenter le paramètre.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Des problèmes se posent pour les paramètres en e/s et en sortie si on utilise le pont jdbc/odbc.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoHeader"><span style= 'font-family:Arial'> </span></p> <h2><a name="node34" id="node34"></a>5.2<span style= 'font:7.0pt "Times New Roman"'> </span> L'exécution de fonctions stockées</h2> <p class="MsoHeader"><span style= 'font-family:Arial'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>C'est le même principe que pour les procédures stockées excepté qu'il y a un paramètre de retour.</span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node35" id="node35"></a>5.2.1<span style= 'font:7.0pt "Times New Roman"'> </span> Syntaxe</h3> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <pre style='margin-left:36.0pt;text-indent:-18.0pt'> <span style='font-size: 12.0pt;font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span><b><u><span style='font-size:12.0pt;font-family:"Times New Roman";color:black'>Préparation :</span></u></b> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Sans paramètres :</span> </pre> <p class="MsoNormal"><b><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement</span></b> <span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB">cStmt = conn.<b>prepareCall</b>("{? = call nom_Fonction}");</span></p> <p class="MsoNormal"><b><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></b></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Avec paramètres :</span></p> <pre> <b><span style= 'font-size:12.0pt;font-family:"Times New Roman"'>CallableStatement</span></b><span style='font-size:12.0pt;font-family:"Times New Roman"'> cStmt = conn.<b>prepareCall</b>("<span style='color:black'>{? = call nom_Fonction (?, ?, ...)}");</span></span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Il faut ensuite valoriser les paramètres au moyen des méthodes set<i>Type</i> (setInt…). Si les paramètres sont uniquement en entrée, on peut indiquer leurs valeurs directement dans l'appel à la méthode prepareCall.</span> </pre> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'> </span> </pre> <pre style='margin-left:36.0pt;text-indent:-18.0pt'> <span style='font-size:12.0pt; font-family:Symbol'>·<span style= 'font:7.0pt "Times New Roman"'> </span></span><b><u><span style='font-size:12.0pt;font-family:"Times New Roman";color:black'>Exécution :</span></u></b> </pre> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>cStmt.executeUpdate();</span></p> <pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman";color:black'>Ensuite, il faut récupérer le paramètre de retour au moyen d'une méthode get<i>Type</i>.</span> </pre> <span style= 'font-size:12.0pt;font-family:"Times New Roman"'><br clear="all" style='page-break-before:always' /></span> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <h3><a name="node36" id="node36"></a><span lang="EN-GB" xml:lang= "EN-GB">5.2.2<span style= 'font:7.0pt "Times New Roman"'> </span></span> <span lang="EN-GB" xml:lang="EN-GB">Exemple</span></h3> <p class="MsoNormal"><span lang="EN-GB" style= 'font-family:"Times New Roman"' xml:lang="EN-GB"> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">CallableStatement cStmt = conn.prepareCall("{? = call MoyenneAnc('SALESMAN')}");</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.registerOutParameter(1,Types.INTEGER); // Obligatoire pour le paramètre de retour</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>cStmt.executeUpdate();</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-family: "Times New Roman"'>System.out.println(cStmt.getInt(1)); // Le paramètre de retour est toujours le 1er paramètre</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'>Code PL/SQL de la fonction :</span></p> <h1 style='margin-left:18.0pt;text-indent:0cm'><span style= 'font-size:12.0pt; font-family:"Times New Roman"'> </span></h1> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">CREATE FUNCTION MoyenneAnc (jobsai varchar(40))</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB">RETURNS int</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB">BEGIN</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> DECLARE ancmoyen int;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang="EN-GB"> select avg(YEAR(sysdate') - YEAR(hiredate))</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> into ancmoyen</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> from emp</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> where job = jobsai;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span lang="EN-GB" style='font-family:"Times New Roman"' xml:lang= "EN-GB"> </span> <span style='font-family:"Times New Roman"'>RETURN ancmoyen;</span></p> <p class="MsoNormal" style= 'text-align:justify;border:none;padding:0cm'><span style= 'font-family:"Times New Roman"'>END MoyenneAnc;</span></p> </div> <p class="MsoNormal"><span style= 'font-family:"Times New Roman"'> </span></p> <p class="MsoNormal"> </p> </div>
Table des matières
Sujets
Recherche
1 Présentation
2 Connexion à la base de données
2.1 Types de pilotes
2.1.1 Pilotes non 100% Java
2.1.1.1 Connexion via jdbc-odbc (type 1)
a) Chargement du pilote
b) Connexion proprement dite
2.1.1.2 Connexion via une API native à installer sur le client (type 2)
a) Chargement du pilote
b) Connexion proprement dite
2.1.2 Pilotes 100% Java
2.1.2.1 Chargement du pilote
2.1.2.2 Connexion proprement dite
3 Le client-serveur de données
3.1 Interrogation de la base
3.1.1 Requête et curseur (ResultSet)
3.1.2 Récupération des données
3.1.3 Exemple global
3.1.4 Méta-données
3.1.5 Requête paramétrée
3.1.6 Requête précompilée
3.1.7 Traitement avancé des ResultSet
3.2 Mise à jour (au sens large) de la base de données
3.2.1 Ajout
3.2.2 Modification
3.2.3 Suppression
3.2.4 Requête de mise à jour (au sens large) paramétrée
3.2.5 Requête de mise à jour (au sens large) précompilée
4 Les requêtes de définition ou de contrôle des données
4.1 La validation/annulation des mises à jour (au sens large) effectuées
5 Le client-serveur de traitements
5.1 L'exécution de procédures stockées
5.1.1 Syntaxe
5.1.2 Exemples
5.2 L'exécution de fonctions stockées
5.2.1 Syntaxe
5.2.2 Exemple
1 Présentation
2.1.1.1 Connexion via jdbc-odbc (type 1)
2.1.1.2 Connexion via une API native à installer sur le client (type 2)
2.1.1 Pilotes non 100% Java
2.1.2.1 Chargement du pilote
2.1.2.2 Connexion proprement dite
2.1.2 Pilotes 100% Java
2.1 Types de pilotes
2 Connexion à la base de données
3.1.1 Requête et curseur (ResultSet)
3.1.2 Récupération des données
3.1.3 Exemple global
3.1.4 Méta-données
3.1.5 Requête paramétrée
3.1.6 Requête précompilée
3.1.7 Traitement avancé des ResultSet
3.1 Interrogation de la base
3.2.1 Ajout
3.2.2 Modification
3.2.3 Suppression
3.2.4 Requête de mise à jour (au sens large) paramétrée
3.2.5 Requête de mise à jour (au sens large) précompilée
3.2 Mise à jour (au sens large) de la base de données
3 Le client-serveur de données
4.1 La validation/annulation des mises à jour (au sens large) effectuées
4 Les requêtes de définition ou de contrôle des données
5.1.1 Syntaxe
5.1.2 Exemples
5.1 L'exécution de procédures stockées
5.2.1 Syntaxe
5.2.2 Exemple
5.2 L'exécution de fonctions stockées
5 Le client-serveur de traitements
a) Chargement du pilote
a) Chargement du pilote
b) Connexion proprement dite
b) Connexion proprement dite
Générée avec
chmProcessor
Générée avec
chmProcessor